package bookStore.dao.business;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import bookStore.dao.DaoTemplate;
import bookStore.po.Book;
import bookStore.vo.BookVo;
import bookStore.vo.BookWithAuthorName;
import bookStore.vo.BookWithPicture;

public class BookDaoImpl extends DaoTemplate implements BookDao{

	public BookDaoImpl(Connection connection) {
		super(connection);
		
	}

	@Override
	public List<Book> queryAll(int offset, int pageSize) {
		return null;
	}

	@Override
	public void save(Book t) {
		this.sql="insert into book values (null,?,?,?,?)";
		try {
			this.queryRunner.update(connection, sql, t.getTitle(),t.getAuthorId(),t.getPicture(),t.getIntroduction());
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@Override
	public Book get(int id) {
		try {
			return this.queryRunner.query(connection, "select * from book where id=?", new BeanHandler<Book>(Book.class), id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public long count() {
		this.sql="select count(*)  from book";
		try {
			long result = this.queryRunner.query(connection, sql, new ScalarHandler<Long>());
			return result;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public void update(Book t) {
		this.sql="update book set title=?,authorId=?,picture=?,introduction=? where id=?";
		try {
			this.queryRunner.update(connection, sql, t.getTitle(),t.getAuthorId(),t.getPicture(),t.getIntroduction(),t.getId());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}

	@Override
	public void delete(int id) {
		this.sql="delete from book where id=?";
		try {
			this.queryRunner.execute(connection, sql, id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@Override
	public List<BookWithPicture> queryBookWithPictureByIdDesc(int offset, int pageSize) {
		this.sql="SELECT * FROM book_by_id_desc limit ?,?";
		try {
			List<BookWithPicture> result = this.queryRunner.query(connection, sql, new BeanListHandler<BookWithPicture>(BookWithPicture.class), offset,pageSize);
			return result;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List<BookWithPicture> queryBookWithPictureByAuthor(int authorId, int offset, int pageSize) {
		this.sql="SELECT * FROM book_by_id_desc " + 
				" WHERE book_by_id_desc.bookId IN(" + 
				" SELECT id " + 
				" FROM book " + 
				"WHERE book.authorId = ?)" + 
				" limit ?,?";
		try {
			List<BookWithPicture> result = this.queryRunner.query(connection, sql, new BeanListHandler<BookWithPicture>(BookWithPicture.class), authorId,offset,pageSize);
			return result;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List<BookWithPicture> queryBookWithPictureByScore(int offset, int pageSize) {
		this.sql="SELECT * FROM book_by_score limit ?,?";
		try {
			List<BookWithPicture> result = this.queryRunner.query(connection, sql, new BeanListHandler<BookWithPicture>(BookWithPicture.class), offset,pageSize);
			return result;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		
		return null;
	}

	@Override
	public List<BookWithPicture> queryBookWithPictureByScoreAndAuthor(int authorId, int offset, int pageSize) {
		this.sql="SELECT * FROM book_by_score " + 
				"WHERE book_by_score.bookId IN(" + 
				"SELECT id " + 
				"FROM book " + 
				"WHERE book.authorId = ?" + 
				")";
		try {
			List<BookWithPicture> result = this.queryRunner.query(connection, sql, new BeanListHandler<BookWithPicture>(BookWithPicture.class), authorId);
			return result;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List<BookWithPicture> queryBookWithPictureByAuthorOrTitle(String authorOrTitle, int offset,int pageSize) {
		this.sql="select book.id as bookId,book.title as title,book.picture as picture "
				+ "from book inner join author on book.authorId=author.id "
				+ "where title like '%"+authorOrTitle+"%' or  author.name like '%"+authorOrTitle+"%' limit ?,?";
		try {
			List<BookWithPicture> result = this.queryRunner.query(connection, sql, new BeanListHandler<BookWithPicture>(BookWithPicture.class),offset,pageSize);
			return result;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public long CountBookWithPictureByAuthorOrTitle(String authorOrTitle) {
		this.sql="select count(*) "
				+ "from book inner join author on book.authorId=author.id "
				+ "where title like '%"+authorOrTitle+"%' or  author.name like '%"+authorOrTitle+"%'";
		try {
			long result = this.queryRunner.query(connection, sql, new ScalarHandler<Long>());
			return result;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public List<BookVo> queryAllBookVo(int offset, int pageSize) {
		try {
			return this.queryRunner.query(connection, "select book.id,title,author.name as authorName from book inner join author on book.authorId=author.id order by book.id desc limit ?,?", new BeanListHandler<BookVo>(BookVo.class), offset,pageSize);
		} catch (SQLException e) {
			e.printStackTrace();
		};
		return null;
	}

	@Override
	public BookWithAuthorName getBookWithAuthorName(int bookId) {
		try {
			return this.queryRunner.query(connection, "select * from book_with_author_name where id=?", new BeanHandler<BookWithAuthorName>(BookWithAuthorName.class), bookId);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}



}
